<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;

class StdToolItemRelController extends Controller
{
    //关联试验项目
    public function relItem(Request $request)
    {
        $id = $request->id; //工器具ID

        if ($request->isMethod('POST')) {
            $action = $request->action;
            if ($action == 'copy') {
                $id = $request->id;
                $id_map = $request->id_map;
                if (!$id || !$id_map) {
                    return '参数错误！';
                }
                $info = DB::table('a_std_tool_item_rel')->find($id);
                $info_map = DB::table('a_std_tool_item_rel')->find($id_map);
                DB::beginTransaction();
                try{
                    //修改报告位置
                    DB::table('a_std_tool_item_rel')->where('id',$id)->update([
                        'pos_start'=>$info_map->pos_start,
                        'pos_end'=>$info_map->pos_end,
                        'pos_date'=>$info_map->pos_date,
                        'pos_tester'=>$info_map->pos_tester,
                        'line_num'=>$info_map->line_num,
                        'jishu_yaoqiu'=>$info_map->jishu_yaoqiu,
                        'manager_id'=>$info_map->manager_id,
                    ]);
                    $testdata_keys = DB::table('a_testdata_key')->where('std_tool_item_rel_id',$id_map)->get(); //试验数据
                    $testdata_keys_insert = [];
                    foreach($testdata_keys as $item){
                        $item = (array)$item;
                        unset($item['id']);
                        $item['std_tool_item_rel_id'] = $id;
                        $item['item_id'] = $info->testitem_id;
                        if(DB::table('a_testdata_key')->where(['std_tool_item_rel_id'=>$id,'data_key'=>$item['data_key']])->doesntExist()){
                            $testdata_keys_insert[] = $item;
                        }
                    }
                    DB::table('a_testdata_key')->insert($testdata_keys_insert);
                    $condition_keys = DB::table('a_condition_key')->where('std_tool_item_rel_id',$id_map)->get(); //试验条件
                    foreach ($condition_keys as $condition_key) {
                        $data = (array)$condition_key;
                        $condition_key_id = $data['id'];
                        unset($data['id']);
                        $data['item_id'] = $info->testitem_id;
                        $data['std_tool_item_rel_id'] = $id;
                        if(DB::table('a_condition_key')->where(['std_tool_item_rel_id'=>$id,'key'=>$data['key']])->doesntExist()){
                            $conditions = DB::table('a_condition')->where('condition_key_id',$condition_key_id)->get(); //试验条件 关联值
                            $condition_key_id2 = DB::table('a_condition_key')->insertGetId($data);
                            foreach($conditions as $item){
                                $item = (array)$item;
                                unset($item['id']);
                                $item['condition_key_id'] = $condition_key_id2;
                                DB::table('a_condition')->insertGetId($item);
                            }
                        }
                    }
                    DB::commit();
                }catch(\Exception $exception){
                    DB::rollBack();
                    dd($data);
                    return $exception->getMessage();
                }
                return 1;
            }
            /* 保存表格-排序 start */
                $data2 = $request->data2;
                if($data2){
                    $count = 0;
                    foreach ($data2 as $id => $updata) {
                        $count += DB::table('a_std_tool_item_rel')->where('id',$id)->update($updata);
                    }
                    return $count;
                }
            /* 保存表格-排序 end */

            $data = $request->only(['std_id', 'testitem_id', 'jishu_yaoqiu']);
            $data['tool_id'] = $id;
            try {
                $maxSort = (int)DB::table('a_std_tool_item_rel')->where('tool_id', $id)->max('sort');
                $data['sort'] = $maxSort + 3;
                //插入数据    insert 返回true 或 false
                $result = DB::table('a_std_tool_item_rel')->insert($data);
            } catch (\Throwable $e) {
                return '2';
            }
            //由于框架自身不支持响应bool值，所以转化一种形式
            return $result ? '1' : '0';
        }
        else {
            //得到 与当前的工器具关联的所有检验标准的 数组
            $arr_std_id = DB::table('a_std_tool_rel')->where('tool_id', $id)->pluck('std_id');
            $data_std = DB::table('a_standard')->whereIn('id', $arr_std_id)->get();
            //获取当前工器具的信息
            $data_tool = DB::table('a_tool')->find($id);
            //获取所有试验项目的信息
            $data_item = DB::table('a_testitem')->get();

            $sql = "SELECT
                        A.id,
                        A.jishu_yaoqiu,
                        A.pos_start,
                        A.pos_end,
                        A.line_num,
                        A.ishide,
                        A.sort,
                        A.std_id,
                        A.station_ids,
                        B.num,
                        C.`name`,
                        D.realname,
                        D.istester,
                        D.`status`
                    FROM
                        a_std_tool_item_rel AS A
                        LEFT JOIN a_standard AS B ON A.std_id = B.id
                        LEFT JOIN a_testitem AS C ON A.testitem_id = C.id
                        LEFT JOIN manager AS D ON A.manager_id = D.id
                    WHERE
                        A.tool_id = :id
                    ORDER BY
                        A.sort ASC,
                        A.std_id ASC";

            $data_rel = DB::select($sql, ['id' => $id]);
            $excel_pos = DB::table('a_excel_pos as a')
                ->leftJoin('a_std_tool_rel as b','a.std_tool_rel_id','=','b.id')
                ->leftJoin('a_standard as c','b.std_id','=','c.id')
                ->where('b.tool_id',$id)
                ->select('a.name as pos_name','a.id as excel_pos_id','c.name as std_name','c.num as std_num','b.std_id','a.is_default')
                ->get();
            $data_excel_pos = [];
            foreach ($excel_pos as $excel_pos_item) {
                $data_excel_pos[$excel_pos_item->std_num][] = $excel_pos_item;
                $excel_pos_id = $excel_pos_item->excel_pos_id;
                // dd($excel_pos_item);
                if($excel_pos_item->is_default == 1 && DB::table('a_excel_pos_std_tool_item_rel')->where('excel_pos_id',$excel_pos_id)->doesntExist()){
                    $std_tool_items = DB::table('a_std_tool_item_rel')->where('std_id',$excel_pos_item->std_id)->where('tool_id',$id)->select('pos_start','pos_end','id as std_tool_item_rel_id')->get();
                    $in = [];
                    foreach ($std_tool_items as $i => $std_tool_item) {
                        $std_tool_item->excel_pos_id = $excel_pos_id;
                        $in[$i] = (array)$std_tool_item;
                    }
                    DB::table('a_excel_pos_std_tool_item_rel')->insert($in);
                }
            }
            $stations = DB::table('a_station')->pluck('name','id');
            return view('admin.stdToolItemRel.relItem', compact('data_std', 'data_tool', 'data_rel', 'data_item','data_excel_pos','stations'));
        }
    }

    //标准--工器具--项目 关系 修改
    public function edit(Request $request)
    {
        $id = $request->input('id');

        if ($request->isMethod('POST')) {
            $data = $request->only(['ishide', 'jishu_yaoqiu', 'manager_id']);
            try {
                //update 返回受影响的行数
                $result = DB::table('a_std_tool_item_rel')->where('id', $id)->update($data);
            } catch (\Throwable $e) {
                return '2';
            }

            //由于框架自身不支持响应bool值，所以转化一种形式
            return $result >= 0 ? '1' : '0';
        }
        else {
            //查询信息
            $sql = "SELECT
                        A.jishu_yaoqiu,
                        A.ishide,
                        A.tool_id,
                        A.std_id,
                        A.manager_id,
                        B.num,
                        C.`name` AS item_name,
                        D.`name` AS tool_name
                    FROM
                        a_std_tool_item_rel AS A
                        LEFT JOIN a_standard AS B ON A.std_id = B.id
                        LEFT JOIN a_testitem AS C ON A.testitem_id = C.id
                        LEFT JOIN a_tool AS D ON A.tool_id = D.id
                    WHERE
                        A.id = :id";

            $info_std_tool_item_rel = DB::select($sql, ['id' => $id]);
            $info_std_tool_item_rel = $info_std_tool_item_rel[0];

            //查询出 状态为启用的 主检人
            $data_tester = DB::table('manager')->where('istester', '1')->where('status', '2')->get();

            //获取当前工器具检测标准小的模板
            $std_tool_rel_id = DB::table('a_std_tool_rel')->where(['std_id'=>$info_std_tool_item_rel->std_id,'tool_id'=>$info_std_tool_item_rel->tool_id])->value('id');
            $excel_pos = DB::table('a_excel_pos')->where('std_tool_rel_id', $std_tool_rel_id)->get();
            return view('admin.stdToolItemRel.edit', compact('info_std_tool_item_rel', 'data_tester','excel_pos'));
        }
    }

    //关联标准--项目 删除
    public function del(Request $request)
    {
        $id = $request->id;
        if (DB::table('a_item_data')->where('item_id',$id)->exists()) {
            return '2';
        }
        $result = DB::table('a_std_tool_item_rel')->where('id', $id)->delete();
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    /**
     * 排序 标准-工器具-试验项目 关联
     */
    public function saveSortStdToolItemRel(Request $request)
    {
        $excel_pos_id = $request->excel_pos_id;
        $updatas = request('data',[]);
        // dd($updatas);
        if (!$excel_pos_id) {
            return '请先选择要修改的模板！';
        }
        $isDef = DB::table('a_excel_pos')->where('id',$excel_pos_id)->value('is_default');
        $stdId = DB::table('a_excel_pos as a')->leftJoin('a_std_tool_rel as b','a.std_tool_rel_id','=','b.id')->where('a.id',$excel_pos_id)->value('b.std_id');
        $tempUp = [
            $stdId => $updatas[$stdId]
        ];
        $updatas = $tempUp;
        unset($tempUp);
        
        DB::beginTransaction();
        try{
            $in_excel_pos_std_tool_item_rel = [];
            foreach ($updatas as $stdId => $updata) {
                foreach ($updata as $relId => $up) {
                    // dd($stdId,$updata,$relId,$up);
                    if (!$excel_pos_id || $isDef==1) {
                        DB::table('a_std_tool_item_rel')->where('id', $relId)->update($up);
                    }
                    $upEpRel = ['pos_start'=>$up['pos_start'],'pos_end'=>$up['pos_end']];
                    $where = [
                            'std_tool_item_rel_id'=>$relId,
                            'excel_pos_id'=>$excel_pos_id ?: $excel_poses[$stdId]->excel_pos_id
                        ];
                    $sql = DB::table('a_excel_pos_std_tool_item_rel')->where($where);
                    if ($sql->exists()) {
                        $sql->update($upEpRel);
                    }else{
                        $in_excel_pos_std_tool_item_rel[] = array_merge($where,$upEpRel);
                    }
                }
            }
            // dd($request->all(),$updata,$excel_poses,$in_excel_pos_std_tool_item_rel);
            if ($in_excel_pos_std_tool_item_rel) {
                DB::table('a_excel_pos_std_tool_item_rel')->insert($in_excel_pos_std_tool_item_rel);
            }
            DB::commit();
        }catch(\Exception $exception){
            DB::rollBack();
            return "操作失败：".$exception->getMessage();
        }
        return 1;
    }

    /**
     * 自动根据ID排序
     */
    public function sort()
    {
        $data = DB::table('a_std_tool_item_rel')->where('sort', 200)->orderBy('id')->get();
        foreach ($data as $item) {
            $maxSort = (int)DB::table('a_std_tool_item_rel')->where('tool_id', $item->tool_id)->max('sort');
            DB::table('a_std_tool_item_rel')->where('id', $item->id)->update(['sort' => $maxSort + 3]);
        }
    }
}
